import pandas as pd
import sqlite3
import pymysql
import sqlalchemy

### 五、数据加载
#### 使用read_csv读取
df1 = pd.read_csv('./kkk', sep='\t', header=None)  # separator 分隔符 默认为 ','
print(df1)

#### 使用read_table读取
df2 = pd.read_table('./kkk', header=None)
print(df2)

#### 使用read_excel读取
df3 = pd.read_excel('./test.xlsx', sheet_name='test')
print(df3)

#### 读取数据库文件
# sqlite3
conn = sqlite3.connect('./test.sqlite')
df4 = pd.read_sql('select * from test', conn, index_col='id')
print(df4)
df4.to_csv('./test.csv')
print(df4.to_dict())
df4.to_json('./test.json')
df4.to_html('./test.html')
# 写入sqlite3的test1表中
df4.to_sql('test1', conn, if_exists='replace')
# mysql
user= input('请输入user:')
password= input('请输入password:')
conn1 = pymysql.connect(host='106.15.205.83', port=3306, user=user, password=password, database='school',
                        charset='utf8')
df5 = pd.read_sql('select * from tb_student', conn1, index_col='stuid')
print(df5)
"""以下用法错误，
df5.to_sql('tb_student1', conn1)
查看文档可知to_sql()函数中
con参数仅支持由
1.sqlalchemy创建的引擎和连接；
2.sqlite3数据库：
con : sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection
            Using SQLAlchemy makes it possible to use any DB supported by that
            library. Legacy support is provided for sqlite3.Connection objects. The user
            is responsible for engine disposal and connection closure for the SQLAlchemy
            connectable See `here \
                <https://docs.sqlalchemy.org/en/13/core/connections.html>`_."""
# sqlalchemy
"""
这个 create_engine() 函数会生成一个 Engine 基于URL的。
数据库URL的典型形式为：
dialect+driver://username:password@host:port/database
"""
engine = sqlalchemy.create_engine('mysql+pymysql://{0}:{1}@106.15.205.83/school'.format(user,password))
df5.to_sql('tb_student1', engine, if_exists='append')
# 根据url获取网络上的数据
df6 = pd.read_csv('http://www-eio.upc.edu/~pau/cms/rdata/csv/datasets/iris.csv')
print(df6.head())

